import pandas as pd
import requests
from lxml import etree
import time
import json
# import pymongo
from my_fake_useragent import UserAgent
ua = UserAgent()
# client = pymongo.MongoClient('localhost',27017)
# mydb = client['gupiao']
# firm_data = mydb['data']

def spider(url):
    headers = {'User-Agent':ua.random}
    res = requests.get(url,params=headers)
    dir = (res.json())[0]
    print(dir)
    data = {
        '债券代码': dir['BONDCODE'],
        '债券全称': dir['FNAME'],
        '债券简称': dir['SNAME'],
        '期限': str(dir['BONDPERIOD'])+'年',
        '发行量': str(dir['ISSUEVOL'])+ '亿',
        '发行价': str(dir['ISSUEPRICE'])+ '元',
        '计息方式': dir['COUPONTYPE'],
        '发行票面利率': dir['COUPONRATE'],
        '上市日期': str(dir['LISTDATE'])[0:10],
        '发行单位': dir['ISSUERNAME'],
        '付息方式': dir['PAYTYPE'],
        # '交易市场': dir['']
        '起息日期':str(dir['ISSUEDATE'])[0:10],
        '到息日期': str(dir['MRTYDATE'])[0:10],
        '发行起始日': str(dir['FRSTVALUEDATE'])[0:10],
        '币种': dir['CURRENCY'],
        '剩余期限': dir['SYQX'],
        '每年付息日': dir['PAYDAY']
    }
    print(data)

if __name__ == '__main__':
    form_data = pd.read_excel('./可转债.xls',sheet_name=0,header=0)
    # print(form_data)
    number = form_data['代码']
    # print(number)
    urls = [' http://dcfm.eastmoney.com/EM_MutiSvcExpandInterface/api/js?type=ZQ_JBXX&token=70f12f2f4f091e459a279469fe49eca5&filter=(BONDCODE%3D%27{}%27)'.format(num)
            for num in number]
    for url in urls:
         spider(url)
    url = ' http://dcfm.eastmoney.com/EM_MutiSvcExpandInterface/api/js?type=ZQ_JBXX&token=70f12f2f4f091e459a279469fe49eca5&filter=(BONDCODE%3D%27113522%27)'
    spider(url)